<?php
/**
 * Created by IntelliJ IDEA.
 * User: Administrator
 * Date: 2018/12/19
 * Time: 4:52
 */

namespace app\magic\service\admin;


use think\Db;

class SystemService
{
    //备份数据库
    public function backups()
    {
        //1.获取数据库信息
        $info = Db::getConfig();
        $dbname = $info['database'];
        //2.获取数据库所有表
        $tables = Db::query("show tables");
        //3、组装头部信息
        header("Content-type:text/html;charset=utf-8");
        $path = BASE_PATH."src/data/";
        $database = $dbname;   //获取当前数据库
        $info  = "-- ----------------------------\r\n";
        $info .= "-- 日期：".date("Y-m-d H:i:s",time())."\r\n";
        $info .= "-- MySQL - 5.5.52-MariaDB : Database - ".$database."\r\n";
        $info .= "-- ----------------------------\r\n\r\n";
        $info .= "SET NAMES utf8;\r\nSET FOREIGN_KEY_CHECKS = 0;\r\n\r\n";
        //4、检查目录是否存在
        if (is_dir($path)) {
            if (is_writable($path)) {
            } else {
                echo '目录不可写'; exit();
            }
        } else {
            mkdir($path,0777,true);
        }
        //5、保存的文件名称
        $file_name = $path.$database.'_'.date('Ymd_His').'.sql';
        file_put_contents($file_name, $info, FILE_APPEND);
        //6、循环表，写入数据
        foreach ($tables as $k => $v) {
            $val = $v["Tables_in_$database"];
            $sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='$val' AND TABLE_SCHEMA='$dbname'";
            $res = Db::query($sql);
            $max_num = Db::table("$val")->order('id desc')->value('id');
            //查询表结构
            $info_table = "-- ----------------------------\r\n";
            $info_table .= "-- Table structure for `$val`\r\n";
            $info_table .= "-- ----------------------------\r\n\r\n";
            $info_table .= "DROP TABLE IF EXISTS `$val`;\r\n";
            if (count($res) < 1) {
                continue;
            }
            $info_table .= "CREATE TABLE `$val` (\n\r\t";
            foreach ($res as $kk => $vv) {
                $info_table .= " `".$vv['COLUMN_NAME']."` ";
                $info_table .= $vv['COLUMN_TYPE'];
                //是否允许空值
                if ($vv['IS_NULLABLE'] == 'NO') {
                    $info_table .= " NOT NULL ";
                }
                //判断主键
                if ($vv['EXTRA']) {
                    $info_table .= " AUTO_INCREMENT ";
                    $key = $vv['COLUMN_NAME'];
                }
                //编码
                if ($vv['CHARACTER_SET_NAME']) {
                    $info_table .= " CHARACTER SET ".$vv['CHARACTER_SET_NAME'];
                }
                //字符集
                if ($vv['COLLATION_NAME']) {
                    $info_table .= " COLLATE ".$vv['COLLATION_NAME'];
                }
                //默认数值
                if ($vv['COLUMN_DEFAULT']) {
                    $info_table .= " DEFAULT ".$vv['COLUMN_DEFAULT'];
                }
                //注释
                if ($vv['COLUMN_COMMENT']) {
                    $info_table .= " COMMENT '".$vv['COLUMN_COMMENT']."',\n\r\t";
                }
            }
            $info_table .= " PRIMARY KEY (`$key`) USING BTREE";
            $info_table .= "\n\r) ENGINE = MyISAM AUTO_INCREMENT $max_num CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\r\n\r\n";

            //查询表数据
            $info_table .= "-- ----------------------------\r\n";
            $info_table .= "-- Data for the table `$val`\r\n";
            $info_table .= "-- ----------------------------\r\n\r\n";
            file_put_contents($file_name,$info_table,FILE_APPEND);
            $sql_data = "select * from $val";
            $data = Db::query($sql_data);
            $count= count($data);
            if ($count < 1) {
                continue;
            }
            foreach ($data as $key => $value) {
                $sqlStr = "INSERT INTO `$val` VALUES (";
                foreach($value as $v_d){
                    $v_d = str_replace("'","\'",$v_d);
                    $sqlStr .= "'".$v_d."', ";
                }
                //需要特别注意对数据的单引号进行转义处理
                //去掉最后一个逗号和空格
                $sqlStr = substr($sqlStr,0,strlen($sqlStr)-2);
                $sqlStr .= ");\r\n";
                file_put_contents($file_name,$sqlStr,FILE_APPEND);
            }
            $info = "\r\n";
            file_put_contents($file_name,$info,FILE_APPEND);
        }
        //7、下载数据到本地
//        ob_end_clean();
//        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
//        header('Content-Description: File Transfer');
//        header('Content-Type: application/octet-stream');
//        header('Content-Length: ' . filesize($file_name));
//        header('Content-Disposition: attachment; filename=' . basename($file_name));
//        readfile($file_name);
//        DownloadFile($path.$file_name);
        return true;
    }

    //数据库文件列表
    public function beifenList(){
        $dir = BASE_PATH."src/data/";    //绝对路径
        $data = $this->getFile($dir);  //获取文件列表
        return $data;
    }
    //还原数据库
    public function do_red($name)
    {
        header("content-type:text/html;charset=utf-8");
        $file = BASE_PATH.'src/data/'.$name;    //还原的SQL文件
        $tablepre = 'magic_';   //表前缀
        if (!file_exists($file)) {
            return ['status'=>-1, 'msg'=>'文件不存在'];
        }
        $sqldata = file_get_contents($file);    //读取数据文件
        $sqlFormat = $this->sql_split($sqldata, $tablepre); //获取SQl语句数组
        //清空原有数据（要做好备份哦）
        $info = Db::getConfig();
        $dbname = $info['database']; //获取当前数据库
        $tables = Db::query("show tables");  //读取所有表
        foreach ($tables as $key => $value) {
            $table = $value["Tables_in_$dbname"];
            Db::execute("delete from $table");
        }
        $counts = count($sqlFormat);
        $arr = [];
        for ($i = $n = 0; $i < $counts; $i++) {
            $sql = trim($sqlFormat[$i]);
            if (strstr($sql, 'INSERT INTO')) {  //读取所有的INSERT语句
                $arr[] = $sql;
                Db::execute($sql);
            }
        }
        return ['status'=>1, 'msg'=>'数据已还原', 'arr'=>$arr];
    }

    function getDir($dir) {
        $dirArray[] =null;
        if(false!=($handle = opendir( $dir))){
            $i=0;
            while (false !== ($file =readdir($handle))){
                if($file != "." && $file != ".."&&!strpos($file,".")){
                    $dirArray[$i] =$file;
                    $i++;
                }
            }
            closedir($handle);
        }
        return $dirArray;
    }

//获取文件列表
    function getFile($dir) {
       $fileArray[] = NULL;
       if (false != ($handle = opendir ( $dir ))) {
           $i=0;
           while ( false !== ($file = readdir ( $handle )) ) {
               if ($file != "." && $file != ".."&&strpos($file,".")) {
                   $fileArray[$i]['url'] = $dir.$file;
                   $fileArray[$i]['name'] = $file;
                   if($i==100){
                       break;
                   }
                   $i++;
               }
           }
           closedir ($handle);
       }
       return $fileArray;
    }



    function sql_split($sql, $tablepre) {
        if ($tablepre != "jd_") {
            $sql = str_replace("jd_", $tablepre, $sql);
        }
        $sql = preg_replace("/TYPE=(InnoDB|MyISAM|MEMORY)( DEFAULT CHARSET=[^; ]+)?/", "ENGINE=\\1 DEFAULT CHARSET=utf8", $sql);
        $sql = str_replace("\r", "\n", $sql);
        $ret = array();
        $num = 0;
        $queriesarray = explode(";\n", trim($sql));
        unset($sql);
        foreach ($queriesarray as $query) {
            $ret[$num] = '';
            $queries = explode("\n", trim($query));
            $queries = array_filter($queries);
            foreach ($queries as $query) {
                $str1 = substr($query, 0, 1);
                if ($str1 != '#' && $str1 != '-')
                    $ret[$num] .= $query;
            }
            $num++;
        }
        return $ret;
    }
}